﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Public;

namespace DAL
{
    public class VatTu_DAL
    {
        Connection conn = new Connection();
        #region "Load cbo Nhom Vat tu hang hoa"
        public DataTable LayDSNhom()
        {
            return conn.LoadData("select MANHOMVATTU, TENNHOMVATTU from NHOMVATTU");
        }
        #endregion

        #region "Load cbo Don vi tinh "
        public DataTable LayDSDVT()
        {
            return conn.LoadData("select * from DONVITINH");
        }
        #endregion

        #region "Load cbo Kho"
        public DataTable LayDSKho()
        {
            return conn.LoadData("select * from KHO");
        }
        #endregion

        #region "Lay don vi chyen doi cua vat tu de nhap"
        public DataTable LayDSDeNhap()
        {
            return conn.LoadData("select * from DONVICHUYENDOI where MAVATTU = '-1'");
        }

        #endregion
        #region "Lay danh sach DVCD theo ma vat tu"
        public DataTable TimMaVatTu(string ma)
        {
            string sql = string.Format("select dvcd.TENDONVICD,dvcd.TYLECD,dvcd.PHEPTINH,dvcd.MOTA,dvcd.DONGIABANLE,dvcd.ID from DONVICHUYENDOI dvcd, VATTU vt where dvcd.MAVATTU = vt.MAVATTU and dvcd.MAVATTU    = '{0}'", ma);
            return conn.LoadData(sql);
        }
        #endregion
        #region "Them Don vi chuyen doi cua vat tu"
        public int ThemDVCD(VatTu_PB vt_pb)
        {
            int nparamater = 7;
            string[] name = new string[nparamater];
            object[] value = new object[nparamater];
            name[0] = "@donvichuyendoi"; value[0] = VatTu_PB.DonViChuyenDoi;
            name[1] = "@mavattu"; value[1] = VatTu_PB.MaVatTu;
            name[2] = "@tylechuyendoi"; value[2] = VatTu_PB.TyLeChuyenDoi;
            name[3] = "@pheptinh"; value[3] = VatTu_PB.PhepTinh;
            name[4] = "@mota"; value[4] = VatTu_PB.MoTa;
            name[5] = "@giaban"; value[5] = VatTu_PB.DonGiaBanLe;
            name[6] = "@id"; value[6] = VatTu_PB.IDDVCD;
            string sql = "insert into DONVICHUYENDOI values (@donvichuyendoi,@mavattu,@tylechuyendoi,@pheptinh,@mota,@giaban,@id)";
            return conn.Update(sql, name, value, nparamater);
        }
        #endregion
        #region "Sua Don vi chuyen doi cua vat tu"
        public int SuaDVCD(VatTu_PB vt_pb)
        {
            int nparamater = 7;
            string[] name = new string[nparamater];
            object[] value = new object[nparamater];
            name[0] = "@donvichuyendoi"; value[0] = VatTu_PB.DonViChuyenDoi;
            name[1] = "@mavattu"; value[1] = VatTu_PB.MaVatTu;
            name[2] = "@tylechuyendoi"; value[2] = VatTu_PB.TyLeChuyenDoi;
            name[3] = "@pheptinh"; value[3] = VatTu_PB.PhepTinh;
            name[4] = "@mota"; value[4] = VatTu_PB.MoTa;
            name[5] = "@giaban"; value[5] = VatTu_PB.DonGiaBanLe;
            name[6] = "@id"; value[6] = VatTu_PB.IDDVCD;
            string sql = "update DONVICHUYENDOI set TENDONVICD = @donvichuyendoi,TYLECD = @tylechuyendoi,PHEPTINH = @pheptinh,MOTA = @mota,DONGIABANLE = @giaban where ID=@id";
            return conn.Update(sql, name, value, nparamater);
        }
        #endregion
        #region "Xoa don vi chuyen doi cua vat tu"
        public int XoaDVCD( string ma)
        {
            int nparamater = 1;
            string[] name = new string[nparamater];
            object[] value = new object[nparamater];
            name[0] = "@mavattu"; value[0] = ma;
            string sql = "delete from DONVICHUYENDOI where MAVATTU = @mavattu";
            return conn.Update(sql, name, value, nparamater);
        }
        
        #endregion


        #region"Lấy danh sách vật tư, hàng hóa"
        public DataTable LayDSVatTu()
        {
            return conn.LoadData("Select * from VATTU");
        }

        #endregion
        #region "Thêm Vật tư, Hàng hóa"
        public int ThemSuaVatTuHangHoa(VatTu_PB vt_pb)
        {
            int nparamater = 12;
            string[] name = new string[nparamater];
            object[] value = new object[nparamater];
            name[0] = "@mavattu"; value[0] = VatTu_PB.MaVatTu;
            name[1] = "@tenvattu"; value[1] = VatTu_PB.TenVatTu;
            name[2] = "@tinhchat"; value[2] = VatTu_PB.TinhChat;
            name[3] = "@nhom"; value[3] = VatTu_PB.Nhom;
            name[4] = "@mota"; value[4] = VatTu_PB.MoTa;
            name[5] = "@donvitinh"; value[5] = VatTu_PB.DonViTinh;
            name[6] = "@thoihanbh"; value[6] = VatTu_PB.ThoiHanBH;
            name[7] = "@soluong"; value[7] = VatTu_PB.SoLuong;
            name[8] = "@nguongoc"; value[8] = VatTu_PB.NguonGoc;
            name[9] = "@kho"; value[9] = VatTu_PB.MaKho;
            name[10] = "@dongiamua"; value[10] = VatTu_PB.DonGiaMua;
            name[11] = "@dongiaban"; value[11] = VatTu_PB.DonGiaBan;


            string sql = @"if exists (select * from VATTU where MAVATTU = @mavattu)
                                update VATTU set TENVATTU = @tenvattu,TINHCHAT=@tinhchat, NHOM = @nhom,MOTA =  @mota, DONVITINH = @donvitinh,THOIHANBH = @thoihanbh, SOLUONG  = @soluong,NGUONGOC  = @nguongoc,MAKHO =@kho,DONGIAMUA = @dongiamua ,DONGIABAN = @dongiaban where MAVATTU=@mavattu
	                        else
                                insert into VATTU values (@mavattu,@tenvattu,@tinhchat,@nhom,@mota,@donvitinh,@thoihanbh,@soluong,@nguongoc,@kho,@dongiamua,@dongiaban)
                            ";



            return conn.Update(sql, name, value, nparamater);
        }

        #endregion
        //#region "Sua Vat tu, Hang hoa"

        //public int SuaVatTu(VatTu_PB vt_pb)
        //{
        //    int nparameter = 12;
        //    string[] name = new string[nparameter];
        //    object[] value = new object[nparameter];
        //    name[0] = "@mavattu"; value[0] = VatTu_PB.MaVatTu;
        //    name[1] = "@tenvattu"; value[1] = VatTu_PB.TenVatTu;
        //    name[2] = "@tinhchat"; value[2] = VatTu_PB.TinhChat;
        //    name[3] = "@nhom"; value[3] = VatTu_PB.Nhom;
        //    name[4] = "@mota"; value[4] = VatTu_PB.MoTa;
        //    name[5] = "@donvitinh"; value[5] = VatTu_PB.DonViTinh;
        //    name[6] = "@thoihanbh"; value[6] = VatTu_PB.ThoiHanBH;
        //    name[7] = "@soluong"; value[7] = VatTu_PB.SoLuong;
        //    name[8] = "@nguongoc"; value[8] = VatTu_PB.NguonGoc;
        //    name[9] = "@kho"; value[9] = VatTu_PB.MaKho;
        //    name[10] = "@dongiamua"; value[10] = VatTu_PB.DonGiaMua;
        //    name[11] = "@dongiaban"; value[11] = VatTu_PB.DonGiaBan;
        //    string sql = "update VATTU set TENVATTU = @tenvattu,TINHCHAT=@tinhchat, NHOM = @nhom,MOTA =  @mota, DONVITINH = @donvitinh,THOIHANBH = @thoihanbh, SOLUONG  = @soluong,NGUONGOC  = @nguongoc,MAKHO =@kho,DONGIAMUA = @dongiamua ,DONGIABAN = @dongiaban where MAVATTU=@mavattu";
        //    return conn.Update(sql, name, value, nparameter);
        //}
        //#endregion
        #region "Xóa Vật tư, Hàng hóa"
        public int XoaVatTu(VatTu_PB vt_pb)
        {
            int nparamater = 12;
            string[] name = new string[nparamater];
            object[] value = new object[nparamater];
            name[0] = "@mavattu"; value[0] = VatTu_PB.MaVatTu;
            name[1] = "@tenvattu"; value[1] = VatTu_PB.TenVatTu;
            name[2] = "@tinhchat"; value[2] = VatTu_PB.TinhChat;
            name[3] = "@nhom"; value[3] = VatTu_PB.Nhom;
            name[4] = "@mota"; value[4] = VatTu_PB.MoTa;
            name[5] = "@donvitinh"; value[5] = VatTu_PB.DonViTinh;
            name[6] = "@thoihanbh"; value[6] = VatTu_PB.ThoiHanBH;
            name[7] = "@soluong"; value[7] = VatTu_PB.SoLuong;
            name[8] = "@nguongoc"; value[8] = VatTu_PB.NguonGoc;
            name[9] = "@kho"; value[9] = VatTu_PB.MaKho;
            name[10] = "@dongiamua"; value[10] = VatTu_PB.DonGiaMua;
            name[11] = "@dongiamban"; value[11] = VatTu_PB.DonGiaBan;

            string sql = "delete from VATTU where MAVATTU=@mavattu";
            return conn.Update(sql, name, value, nparamater);
        }
        public int XoaVatTu(string ma)
        {
            int nparamater = 1;
            string[] name = new string[nparamater];
            object[] value = new object[nparamater];
            name[0] = "@mavattu"; value[0] = ma;

            string sql = "delete from VATTU where MAVATTU=@mavattu";
            return conn.Update(sql, name, value, nparamater);
        }
        #endregion
        public bool Exists(string ma)
        {
            string sql = string.Format("select * from VATTU where MAVATTU = '{0}'", ma);
            DataTable dt = conn.LoadData(sql);
            if (dt == null) return false;

            return dt.Rows.Count > 0;
        }

        #region "Tăng mã tự động tồn kho"
        public string NextID_TK()
        {
            return NextID_PB.NextID(conn.GetLastID("DONVICHUYENDOI", "ID"), "DVCD");
        }
        #endregion

    }
}
